package j.util.jdbc;

import j.data.PagedData;
import j.util.New;
import j.util.StringUtils;
import j.util.TwoTuple;
import j.util.jdbc.simple.core.SimpleJdbcContext;
import j.util.jdbc.simple.core.operations.SimpleJdbcQueryOperations;

import javax.sql.DataSource;
import java.sql.*;
import java.util.List;
import java.util.Map;

/**
 * User: jason
 * Date: 13-7-2 上午9:45
 */
public class QueryJdbcTemplate extends JdbcTemplate {

    //查询操作
    private SimpleJdbcQueryOperations query;

    public QueryJdbcTemplate(DataSource ds) {
        super(ds);
        try{
            conn = dataSource.getConnection();
        }catch(SQLException e){
            close();
            log.error(String.format("获取数据库连接时失败,异常信息:%s.",e.getMessage()));
            throw new RabbitJdbcException(String.format("SingleConnectionJdbcTemplate在获取数据库链接时出现异常,异常信息:%s.",e.getMessage()));
        }
        this.query = SimpleJdbcContext.getQueryInstance();
    }

    public void close(){
        log.info("关闭数据库连接");
        JdbcUtils.free(conn);
    }

    private Connection conn;

    @Override
    public <T> T queryForDomain(String sql, RowMapper<T> rowMapper, Object... args) {
        log.debug(String.format("在共享连接中执行单数据查询sql语句：%s，参数：%s.",sql,getArgsInfo(args)));
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try{
            stmt = conn.prepareStatement(sql);
            JdbcUtils.setStatementArguments(stmt, args);
            rs = stmt.executeQuery();
            if(rs.next()){
                return rowMapper.mapRow(rs,0);
            }
        }catch(SQLException e){
            log.error(String.format("在共享连接中执行单数据查询sql语句：%s时出现异常：%s.", sql, e.getMessage()));
            throw new RabbitJdbcException(String.format("JdbcTemplate.queryForDomain在共享连接中执行单数据查询时出现异常：%s.",e.getMessage()));
        }finally{
            JdbcUtils.free(null, stmt, rs);
        }
        log.debug(String.format("在共享连接中执行单数据查询sql语句：%s时未获取到一条记录.",sql));
        return null;
    }

    @Override
    public void queryForDomain(String sql, ResultSetHandler resultSetHandler, Object... args) {
        log.debug(String.format("在共享连接中执行自定义ResultSetHandler的单数据查询sql语句：%s，参数：%s.",sql,getArgsInfo(args)));
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try{
            stmt = conn.prepareStatement(sql);
            JdbcUtils.setStatementArguments(stmt, args);
            rs = stmt.executeQuery();
            if(rs.next()){
                resultSetHandler.doResultSet(rs);
            }
        }catch(SQLException e){
            log.error(String.format("在共享连接中执行自定义ResultSetHandler的单数据查询sql语句【%s】时出现异常：%s.", sql, e.getMessage()));
            throw new RabbitJdbcException(String.format("JdbcTemplate.queryForDomain在共享连接中执行数据查询时出现异常：%s.",e.getMessage()));
        }finally{
            JdbcUtils.free(null, stmt, rs);
        }
    }

    @Override
    public Map<String, Object> queryForMap(String sql, Object... args) {
        log.debug(String.format("在共享连接中执行单数据查询(Map)sql语句：%s，参数：%s.",sql,getArgsInfo(args)));
        Map<String,Object> map = New.map();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try{
            stmt = conn.prepareStatement(sql);
            JdbcUtils.setStatementArguments(stmt, args);
            rs = stmt.executeQuery();
            if(rs.next()){
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    String key = rsmd.getColumnName(i);
                    Object obj = JdbcUtils.getResultSetValue(rs, i);
                    map.put(key, obj);
                }
            }
        }catch(SQLException e){
            log.error(String.format("在共享连接中执行单数据查询(Map)sql语句：%s时出现异常：%s.", sql, e.getMessage()));
            throw new RabbitJdbcException(String.format("JdbcTemplate.queryForDomain在共享连接中执行单数据查询时出现异常：%s.",e.getMessage()));
        }finally{
            JdbcUtils.free(null, stmt, rs);
        }
        return map;
    }

    @Override
    public List<Map<String, Object>> queryForListMap(String sql, Object... args) {
        log.debug(String.format("在共享连接中执行数据集查询(List<Map>)sql语句：%s，参数：%s.",sql,getArgsInfo(args)));
        List<Map<String,Object>> list = New.list();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try{
            stmt = conn.prepareStatement(sql);
            JdbcUtils.setStatementArguments(stmt, args);
            rs = stmt.executeQuery();
            ResultSetMetaData rsmd;
            Map<String,Object> map;
            String key;
            Object objV;
            int columnCount = 0,i;
            while(rs.next()){
                rsmd = rs.getMetaData();
                if(columnCount==0){
                    columnCount = rsmd.getColumnCount();
                }
                map = New.map();
                for (i = 1; i <= columnCount; i++) {
                    key = rsmd.getColumnName(i);
                    objV = JdbcUtils.getResultSetValue(rs, i);
                    map.put(key, objV);
                }
                list.add(map);
            }
        }catch(SQLException e){
            log.error(String.format("在共享连接中执行数据集查询(List<Map>)sql语句：%s时出现异常：%s.", sql, e.getMessage()));
            throw new RabbitJdbcException(String.format("JdbcTemplate.queryForListMap在共享连接中执行数据集(List<Map>)查询时出现异常：%s.",e.getMessage()));
        }finally{
            JdbcUtils.free(null, stmt, rs);
        }
        return list;
    }

    /**
     * 发页形式查询，以List<Map封装数据
     * @param sql
     * @param pageIndex
     * @param pageSize
     * @param args
     * @return
     */
    @Override
    public PagedData<List<Map<String, Object>>> queryForPaging(String sql, int pageIndex, int pageSize, Object... args) {
        log.debug(String.format("在QueryJdbcTemplate中执行以ListMap封装数据的分页方法，sql:%s，参数：%s.",sql,getArgsInfo(args)));
        TwoTuple<String,Object[]> pagingArgs = getCorrectGetPagingSql(sql,pageIndex,pageSize,args);
        if(StringUtils.isNullOrEmpty(pagingArgs.first)){
            throw new RabbitJdbcException("没有获取到当前数据库管理系统的类型!");
        }
        String pagingSql = pagingArgs.first;
        int totalList = 0;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try{
            //先计算总记录数
            boolean isComplex = sql.contains("@@");
            stmt = conn.prepareStatement(JdbcUtils.formatSqlToCount(sql,isComplex));
            JdbcUtils.setStatementArguments(stmt, args);
            rs = stmt.executeQuery();
            if(rs.next()){
                totalList = rs.getInt(1);
            }
            rs.close();stmt.close();
            if(isComplex){
                pagingSql = pagingSql.replaceFirst("@@","");
            }
            stmt = conn.prepareStatement(pagingSql);
            JdbcUtils.setStatementArguments(stmt, pagingArgs.second);
            rs = stmt.executeQuery();
            List<Map<String,Object>> listMap = New.list();
            ResultSetMetaData rsmd;
            Map<String,Object> map;
            String key;
            Object objV;
            int columnCount = 0,i;
            while(rs.next()){
                rsmd = rs.getMetaData();
                if(columnCount==0){
                    columnCount = rsmd.getColumnCount();
                }
                map = New.map();
                for (i = 1; i <= columnCount; i++) {
                    key = rsmd.getColumnName(i);
                    objV = JdbcUtils.getResultSetValue(rs, i);
                    map.put(key, objV);
                }
                listMap.add(map);
            }
            return new PagedData<List<Map<String,Object>>>(pageIndex,pageSize,totalList,listMap);
        }catch(SQLException e){
            throw new RabbitJdbcException(String.format("在QueryJdbcTemplate中执行以ListMap封装数据的分页方法时出现异常：%s.",e.getMessage()));
        }finally{
            JdbcUtils.free(null, stmt, rs);
        }
    }

    @Override
    public <T> PagedData<List<T>> queryForPaging(String sql, RowMapper<T> rowMapper, int pageIndex, int pageSize, Object... args) {
        log.debug(String.format("在共享连接中以分页形式查询记录集,当前页:%s,页大小:%s,sql:%s,参数:%s.",pageIndex,pageSize,sql,getArgsInfo(args)));
        if(pageIndex<=0){
            pageIndex = 1;
        }
        if(pageSize<=0){
            pageSize = DEFAULT_PAGE_SIZE;
        }
        Object[] _args = new Object[args.length+2];
        int i;
        for(i=0;i<args.length;i++){
            _args[i] = args[i];
        }
        String _sql = null;
        DatabaseType dbType = JdbcPlatform.getDatabaseType(dataSource);
        if(dbType.equals(DatabaseType.MYSQL)){
            //mysql数据库
            _args[i++] = (pageIndex-1)*pageSize;
            _args[i] = pageSize;
            _sql = String.format(JdbcTemplate.MYSQL_PAGING_TEMPLATE,sql);
        }else if(dbType.equals(DatabaseType.MSSQL)){
            _sql = String.format(JdbcTemplate.MSSQL_PAGING_TEMPLATE,sql);
            _args[i++] = (pageIndex-1)*pageSize;
            _args[i] = pageSize;
        }else if(dbType.equals(DatabaseType.ORACLE)){
            _sql = String.format(JdbcTemplate.ORACLE_PAGING_TEMPLATE,sql);
            _args[i++] = (pageIndex-1)*pageSize+1;
            _args[i] = pageSize;
        }
        if(StringUtils.isNullOrEmpty(_sql)){
            throw new RabbitJdbcException("没有获取到当前数据库管理系统的类型!");
        }
        int totalList = 0;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try{
            //先计算总记录数
            boolean isComplex = sql.contains("@@");
            stmt = conn.prepareStatement(JdbcUtils.formatSqlToCount(sql,isComplex));
            JdbcUtils.setStatementArguments(stmt, args);
            rs = stmt.executeQuery();
            if(rs.next()){
                totalList = rs.getInt(1);
            }
            rs.close();stmt.close();
            if(isComplex){
                _sql = _sql.replaceFirst("@@","");
            }
            stmt = conn.prepareStatement(_sql);
            JdbcUtils.setStatementArguments(stmt, _args);
            rs = stmt.executeQuery();
            List<T> list = New.list();
            i = 0;
            while(rs.next()){
                list.add(rowMapper.mapRow(rs,i++));
            }
            return new PagedData<List<T>>(pageIndex,pageSize,totalList,list);
        }catch(SQLException e){
            log.error(String.format("在共享连接中以分页形式查询记录集时出现异常：%s,sql语句【%s】.", sql, e.getMessage()));
            throw new RabbitJdbcException(String.format("JdbcTemplate.queryForList在共享连接中执行数据查询时出现异常：%s.",e.getMessage()));
        }finally{
            JdbcUtils.free(null, stmt, rs);
        }
    }

    @Override
    public <T> List<T> queryForList(String sql, RowMapper<T> rowMapper, Object... args) {
        log.debug(String.format("在共享连接中执行自定义RowMapper的记录集查询sql语句：%s，参数：%s.",sql,getArgsInfo(args)));
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try{
            stmt = conn.prepareStatement(sql);
            JdbcUtils.setStatementArguments(stmt, args);
            rs = stmt.executeQuery();
            List<T> list = New.list();
            int index = 0;
            while(rs.next()){
                list.add(rowMapper.mapRow(rs,index++));
            }
            return list;
        }catch(SQLException e){
            log.error(String.format("在共享连接中执行自定义RowMapper的记录集查询sql语句【%s】时出现异常：%s.", sql, e.getMessage()));
            throw new RabbitJdbcException(String.format("JdbcTemplate.queryForList在共享连接中执行数据查询时出现异常：%s.",e.getMessage()));
        }finally{
            JdbcUtils.free(null, stmt, rs);
        }
    }

    @Override
    public void queryForList(String sql, ResultSetHandler resultSetHandler, Object... args) {
        log.debug(String.format("在共享连接中执行自定义ResultSetHandler的记录集查询sql语句：%s，参数：%s.",sql,getArgsInfo(args)));
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try{
            stmt = conn.prepareStatement(sql);
            JdbcUtils.setStatementArguments(stmt, args);
            rs = stmt.executeQuery();
            while(rs.next()){
                resultSetHandler.doResultSet(rs);
            }
        }catch(SQLException e){
            log.error(String.format("在共享连接中执行自定义ResultSetHandler的记录集查询sql语句【%s】时出现异常：%s.", sql, e.getMessage()));
            throw new RabbitJdbcException(String.format("JdbcTemplate.queryForList(自处理结集)在共享连接中执行数据查询时出现异常：%s.",e.getMessage()));
        }finally{
            JdbcUtils.free(null, stmt, rs);
        }
    }

    @Override
    public int queryForInt(String sql, Object... args) {
        log.debug(String.format("在共享连接中执行整形数据sql语句：%s，参数：%s.",sql,getArgsInfo(args)));
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try{
            stmt = conn.prepareStatement(sql);
            JdbcUtils.setStatementArguments(stmt, args);
            rs = stmt.executeQuery();
            if(rs.next()){
                return rs.getInt(1);
            }else{
                throw new RabbitJdbcException(String.format("在共享连接中queryForInt没有查询到任何数据"));
            }
        }catch(SQLException e){
            log.error(String.format("在共享连接中执行整形数据sql语句：%s时出现异常：%s.", sql, e.getMessage()));
            throw new RabbitJdbcException(String.format("JdbcTemplate.queryForInt在共享连接中执行整形数据查询时出现异常：%s.",e.getMessage()));
        }finally{
            JdbcUtils.free(null, stmt, rs);
        }
    }

    @Override
    public Object queryForObject(String sql, Object... args) {
        log.debug(String.format("在共享连接中执行单结果sql语句：%s，参数：%s.",sql,getArgsInfo(args)));
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try{
            stmt = conn.prepareStatement(sql);
            JdbcUtils.setStatementArguments(stmt, args);
            rs = stmt.executeQuery();
            if(rs.next()){
                return rs.getObject(1);
            }
        }catch(SQLException e){
            log.error(String.format("在共享连接中执行单结果sql语句：%s时出现异常：%s.",sql,e.getMessage()));
            throw new RabbitJdbcException(String.format("JdbcTemplate.queryForInt在共享连接中执行单结果查询时出现异常：%s.",e.getMessage()));
        }finally{
            JdbcUtils.free(null, stmt, rs);
        }
        return null;
    }

    @Override
    public int execute(String sql, Object... args) {
        throw new RabbitJdbcException("QueryJdbcTemplate不支持此方法.");
    }

    @Override
    public int[] executeBatch(String sql, List<Object[]> argsList) {
        throw new RabbitJdbcException("QueryJdbcTemplate不支持此方法.");
    }

    @Override
    public int executeInsert(String tableName, Map<String, Object> columnValuesMap) {
        throw new RabbitJdbcException("QueryJdbcTemplate不支持此方法.");
    }

    @Override
    public int executeUpdate(String tableName, Map<String, Object> updatedColumnValuesMap, String whereSql, Object... whereArgs) {
        throw new RabbitJdbcException("QueryJdbcTemplate不支持此方法.");
    }

    @Override
    public int insert(Object object) {
        throw new RabbitJdbcException("QueryJdbcTemplate不支持此方法.");
    }

    @Override
    public Number insertAndReturnKey(Object object) {
        throw new RabbitJdbcException("QueryJdbcTemplate不支持此方法.");
    }

    @Override
    public int[] insertBatch(Class<?> clazz, List<?> objects) {
        throw new RabbitJdbcException("QueryJdbcTemplate不支持此方法.");
    }

    @Override
    public <T> int delete(T t) {
        throw new RabbitJdbcException("QueryJdbcTemplate不支持此方法.");
    }

    @Override
    public <T> int[] deleteBatch(List<T> list) {
        throw new RabbitJdbcException("QueryJdbcTemplate不支持此方法.");
    }

    @Override
    public <T> int delete(Class<T> clazz, Map<String, Object> args) {
        throw new RabbitJdbcException("QueryJdbcTemplate不支持此方法.");
    }

    @Override
    public <T> int deleteByCW(Class<T> clazz, Map<String, Object> args) {
        throw new RabbitJdbcException("QueryJdbcTemplate不支持此方法.");
    }

    @Override
    public <T> T findByPk(Class<T> requiredType, Map<String, Object> args) {
        try{
            return query.findByPk(conn,requiredType, args);
        }catch(SQLException e){
            String msg = String.format("在共享连接中执行findByPk[%s]时异常：%s.", requiredType.getName(), e.getMessage());
            log.error(msg);
            throw new RabbitJdbcException(msg);
        }
    }

    @Override
    public <T> List<T> queryForList(Class<T> requiredType, Map<String, Object> args, Map<String, String> orders) {
        try{
            return query.queryList(conn,requiredType, args, orders);
        }catch(SQLException e){
            String msg = String.format("在共享连接中执行queryList[%s]时异常：%s.", requiredType.getName(), e.getMessage());
            log.error(msg);
            throw new RabbitJdbcException(msg);
        }
    }

    @Override
    public <T> PagedData<List<T>> queryForPaging(Class<T> requiredType, int pageIndex, int pageSize, Map<String, Object> args, Map<String, String> orders) {
        try{
            return query.queryList(conn,requiredType, pageIndex, pageSize, args, orders);
        }catch(SQLException e){
            String msg = String.format("在共享连接中执行queryList[%s]时异常：%s.", requiredType.getName(), e.getMessage());
            log.error(msg);
            throw new RabbitJdbcException(msg);
        }
    }

    @Override
    public <T> int update(Class<T> clazz, Map<String, Object> updatedData) {
        throw new RabbitJdbcException("QueryJdbcTemplate不支持此方法.");
    }

    @Override
    public <T> int update(T t) {
        throw new RabbitJdbcException("QueryJdbcTemplate不支持此方法.");
    }

    @Override
    public <T> int update(Class<T> clazz, Map<String, Object> updatedData, Map<String, Object> where) {
        throw new RabbitJdbcException("QueryJdbcTemplate不支持此方法.");
    }

    @Override
    public <T> int[] updateBatch(Class<T> clazz, List<T> list) {
        throw new RabbitJdbcException("QueryJdbcTemplate不支持此方法.");
    }
}
